**************************************************************************************************************************************************
****************************** Wealth-Income Ratios in Free Market Capitalism: Switzerland, 1900-2020 ********************************************
**************************************************************************************************************************************************

clear all
set more off
set scheme s1color  
cap set scheme mygraphs

**Author: Enea Baselgia and Isabel Z. Martinez
**Date: January, 25 2023

*** set this path to the current directory
*** global mypath "C:\Users\EBaselgia\Dropbox\WIR_project\Publication_process\replication"

*** EXTENSION C: CROSS-COUNTRY EVIDENCE ON WIR AND HOUSING PRICES ***

* READ IN STOCK MARKET INDICES, HOUSE PRICE INDICES AND WIRS FOR SELECTED COUNTRIES*
/* countries: CH, US, FR, IT, SW, DE, ES, UK
	long series stock indices: OECD series, MSCI
	housing series: OECD series (real and nomoinal); single cou try series
*/



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* READ STOCK PRICE INDICES *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
cd "$mypath/raw_data/extension_C"

* MSCI
import excel using "MSCI-world.xlsx", sheet("History Index") cellrange(A7:B615) firstrow clear
label var WORLDStandardLargeMidCap "MSCI World"
rename WORLDStandardLargeMidCap MSCI


//replace last trading day with last day of the month
gen Date2 = .
format Date2 %td
forvalues y = 1970/2020 {
forvalues m = 1/12 {
local last_day = mdy(`m'+1,1,`y')-1
display %td `last_day'
replace Date2 = `last_day' if year(Date)==`y' & month(Date) == `m'

local yearend = mdy(`m', 31, `y')
replace Date2 = `yearend' if year(Date)==`y' & month(Date) == 12 & Date2 ==.
}
}
drop Date
rename Date2 Date


gen MSCI_2010 =.
replace MSCI_2010 = 100 if Date==td(31jan2010)
replace MSCI_2010 = 100/ MSCI[482]*MSCI
replace MSCI = MSCI_2010
drop MSCI_2010
gen year=year(Date)
drop if year==.
collapse MSCI, by(year)


tempfile msci
save "`msci'"




* add OECD Stock market data
import excel using "OECD_SharePrices.xlsx", sheet("TotalSharePrices") firstrow clear

keep if LOCATION=="CHE" | LOCATION=="DEU" | LOCATION=="SWE" | LOCATION=="ITA" | LOCATION=="FRA" | LOCATION=="USA" | LOCATION=="ESP" | LOCATION=="GBR" | LOCATION=="JPN" | LOCATION=="CAN" | LOCATION=="NOR" | LOCATION=="AUS"
keep if FREQUENCY=="A"
drop INDICATOR SUBJECT MEASURE FREQUENCY FlagCodes
rename TIME year
rename Value shareprices_
destring year, replace
reshape wide shareprices_, i(year) j(LOCATION) string
gen Date = mdy(6, 30, year)


// normalize everything to 2010
gen sortm = (year(Date)==2010)
sort sortm

foreach var in shareprices_AUS shareprices_CAN shareprices_CHE shareprices_DEU shareprices_ESP shareprices_FRA shareprices_GBR shareprices_ITA shareprices_JPN shareprices_NOR shareprices_SWE shareprices_USA{
replace `var'= `var'/`var'[_N]*100
}
drop sortm 
sort Date
drop Date


tempfile SharePrices
save "`SharePrices'"





* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* REAL Housing DATA *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* add OECD Real Estate data
import excel using "OECD_HousePrices.xlsx", sheet("TotalHousePrices") firstrow clear
keep if LOCATION=="CHE" | LOCATION=="DEU" | LOCATION=="SWE" | LOCATION=="ITA" | LOCATION=="FRA" | LOCATION=="USA" | LOCATION=="ESP" | LOCATION=="GBR" | LOCATION=="JPN" | LOCATION=="CAN" | LOCATION=="NOR" | LOCATION=="AUS"
keep if FREQUENCY=="A"
drop INDICATOR SUBJECT MEASURE FREQUENCY FlagCodes
rename TIME year
rename Value houseprices_
destring year, replace
reshape wide houseprices_, i(year) j(LOCATION) string
gen Date = mdy(6, 30, year)


// normalize everything to 2010
gen sortm = (year(Date)==2010)
sort sortm


foreach var in houseprices_AUS houseprices_CAN houseprices_CHE houseprices_DEU houseprices_ESP houseprices_FRA houseprices_GBR houseprices_ITA houseprices_JPN houseprices_NOR houseprices_SWE houseprices_USA{
replace `var'= `var'/`var'[_N]*100
}
drop sortm 
sort Date
drop Date


tempfile HousePrices
save "`HousePrices'"









* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* OECD Nominal Housing DATA *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* add OECD Real Estate data
import excel using "OECD_HousePrices_nom.xlsx", sheet("TotalHousePrices_nom") firstrow clear
keep if LOCATION=="CHE" | LOCATION=="DEU" | LOCATION=="SWE" | LOCATION=="ITA" | LOCATION=="FRA" | LOCATION=="USA" | LOCATION=="ESP" | LOCATION=="GBR" | LOCATION=="JPN" | LOCATION=="CAN" | LOCATION=="NOR" | LOCATION=="AUS"
keep if FREQUENCY=="A"
keep if SUBJECT=="NOMINAL"
drop INDICATOR SUBJECT MEASURE FREQUENCY FlagCodes
rename TIME year
rename Value houseprices_nom_
destring year, replace
reshape wide houseprices_nom_, i(year) j(LOCATION) string
gen Date = mdy(6, 30, year)


// normalize everything to 2010
gen sortm = (year(Date)==2010)
sort sortm


foreach var in houseprices_nom_AUS houseprices_nom_CAN houseprices_nom_CHE houseprices_nom_DEU houseprices_nom_ESP houseprices_nom_FRA houseprices_nom_GBR houseprices_nom_ITA houseprices_nom_JPN houseprices_nom_NOR houseprices_nom_SWE houseprices_nom_USA{
replace `var'= `var'/`var'[_N]*100
}
drop sortm 
sort Date
drop Date


tempfile HousePrices_nom
save "`HousePrices_nom'"







* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* OECD CPI *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* add OECD Real Estate data
import excel using "OECD_CPI.xlsx", sheet("CPI_Total") firstrow clear
keep if LOCATION=="CHE" | LOCATION=="DEU" | LOCATION=="SWE" | LOCATION=="ITA" | LOCATION=="FRA" | LOCATION=="USA" | LOCATION=="ESP" | LOCATION=="GBR" | LOCATION=="JPN" | LOCATION=="CAN" | LOCATION=="NOR" | LOCATION=="AUS"
keep if FREQUENCY=="A"
keep if MEASURE=="IDX2015"
keep if SUBJECT=="TOT"

drop INDICATOR SUBJECT MEASURE FREQUENCY FlagCodes
rename TIME year
rename Value cpi_
destring year, replace
reshape wide cpi_, i(year) j(LOCATION) string
gen Date = mdy(6, 30, year)


// normalize everything to 2010
gen sortm = (year(Date)==2010)
sort sortm


foreach var in cpi_AUS cpi_CAN cpi_CHE cpi_DEU cpi_ESP cpi_FRA cpi_GBR cpi_ITA cpi_JPN cpi_NOR cpi_SWE cpi_USA{
replace `var'= `var'/`var'[_N]*100
}
drop sortm 
sort Date
drop Date


tempfile cpi
save "`cpi'"




/// Generate Full Data-Set

merge 1:1 year using "`HousePrices_nom'", nogen
merge 1:1 year using "`HousePrices'", nogen
merge 1:1 year using "`SharePrices'", nogen
merge 1:1 year using "`msci'", nogen

cd "$mypath/final_data"
merge 1:1 year using "WIR_final.dta", nogen

keep year cpi_AUS cpi_CAN cpi_CHE cpi_DEU cpi_ESP cpi_FRA cpi_GBR cpi_ITA cpi_JPN cpi_NOR cpi_SWE cpi_USA houseprices_nom_AUS houseprices_nom_CAN houseprices_nom_CHE houseprices_nom_DEU houseprices_nom_ESP houseprices_nom_FRA houseprices_nom_GBR houseprices_nom_ITA houseprices_nom_JPN houseprices_nom_NOR houseprices_nom_SWE houseprices_nom_USA houseprices_AUS houseprices_CAN houseprices_CHE houseprices_DEU houseprices_ESP houseprices_FRA houseprices_GBR houseprices_ITA houseprices_JPN houseprices_NOR houseprices_SWE houseprices_USA shareprices_AUS shareprices_CAN shareprices_CHE shareprices_DEU shareprices_ESP shareprices_FRA shareprices_GBR shareprices_ITA shareprices_JPN shareprices_NOR shareprices_SWE shareprices_USA MSCI ch_nni_T_R AU_nni_T_R DE_nni_T_R FR_nni_T_R ES_nni_T_R IT_nni_T_R GB_nni_T_R SE_nni_T_R US_nni_T_R JP_nni_T_R NO_nni_T_R CA_nni_T_R ch_pWIR AU_pWIR AU_pWIR_excl_domW_agriW DE_pWIR DE_pWIR_excl_domW_agriW FR_pWIR FR_pWIR_excl_domW_agriW ES_pWIR ES_pWIR_excl_domW_agriW IT_pWIR IT_pWIR_excl_dom IT_pWIR IT_pWIR_excl_domW_agriW GB_pWIR GB_pWIR_excl_domW_agriW SE_pWIR SE_pWIR_excl_domW_agriW US_pWIR US_pWIR_excl_domW_agriW JP_pWIR JP_pWIR_excl_domW_agriW NO_pWIR NO_pWIR_excl_domW_agriW CA_pWIR CA_pWIR_excl_domW_agriW 





//Rename variables & save final data
 rename houseprices_AUS au_houseP_real
 rename houseprices_CAN ca_houseP_real
 rename houseprices_CHE ch_houseP_real
 rename houseprices_DEU de_houseP_real
 rename houseprices_ESP es_houseP_real
 rename houseprices_FRA fr_houseP_real
 rename houseprices_GBR gb_houseP_real
 rename houseprices_ITA it_houseP_real
 rename houseprices_JPN jp_houseP_real
 rename houseprices_NOR no_houseP_real
 rename houseprices_SWE se_houseP_real
 rename houseprices_USA us_houseP_real
 
 
 rename houseprices_nom_AUS au_houseP_nom
 rename houseprices_nom_CAN ca_houseP_nom
 rename houseprices_nom_CHE ch_houseP_nom
 rename houseprices_nom_DEU de_houseP_nom
 rename houseprices_nom_ESP es_houseP_nom
 rename houseprices_nom_FRA fr_houseP_nom
 rename houseprices_nom_GBR gb_houseP_nom
 rename houseprices_nom_ITA it_houseP_nom
 rename houseprices_nom_JPN jp_houseP_nom
 rename houseprices_nom_NOR no_houseP_nom
 rename houseprices_nom_SWE se_houseP_nom
 rename houseprices_nom_USA us_houseP_nom
 
 
 rename shareprices_AUS au_shareP_nom
 rename shareprices_CAN ca_shareP_nom
 rename shareprices_CHE ch_shareP_nom
 rename shareprices_DEU de_shareP_nom
 rename shareprices_ESP es_shareP_nom
 rename shareprices_FRA fr_shareP_nom
 rename shareprices_GBR gb_shareP_nom
 rename shareprices_ITA it_shareP_nom
 rename shareprices_JPN jp_shareP_nom
 rename shareprices_NOR no_shareP_nom
 rename shareprices_SWE se_shareP_nom
 rename shareprices_USA us_shareP_nom
 
 
 rename cpi_AUS au_cpi
 rename cpi_CAN ca_cpi
 rename cpi_CHE ch_cpi
 rename cpi_DEU de_cpi
 rename cpi_ESP es_cpi
 rename cpi_FRA fr_cpi
 rename cpi_GBR gb_cpi
 rename cpi_ITA it_cpi
 rename cpi_JPN jp_cpi
 rename cpi_NOR no_cpi
 rename cpi_SWE se_cpi
 rename cpi_USA us_cpi
 
 
 rename DE_pWIR de_pWIR_offici
 rename FR_pWIR fr_pWIR_offici
 rename GB_pWIR gb_pWIR_offici
 rename IT_pWIR it_pWIR_offici
 rename SE_pWIR se_pWIR_offici
 rename US_pWIR us_pWIR_offici
 rename AU_pWIR au_pWIR_offici
 rename CA_pWIR ca_pWIR_offici
 rename ES_pWIR es_pWIR_offici
 rename JP_pWIR jp_pWIR_offici
 rename NO_pWIR no_pWIR_offici
 
rename AU_pWIR_excl_domW_agriW au_pWIR
rename DE_pWIR_excl_domW_agriW de_pWIR
rename FR_pWIR_excl_domW_agriW fr_pWIR
rename ES_pWIR_excl_domW_agriW es_pWIR
rename IT_pWIR_excl_domW_agriW it_pWIR
rename GB_pWIR_excl_domW_agriW gb_pWIR
rename SE_pWIR_excl_domW_agriW se_pWIR
rename US_pWIR_excl_domW_agriW us_pWIR
rename JP_pWIR_excl_domW_agriW jp_pWIR
rename NO_pWIR_excl_domW_agriW no_pWIR
rename CA_pWIR_excl_domW_agriW ca_pWIR

rename AU_nni_T_R au_nni_T_R
rename DE_nni_T_R de_nni_T_R
rename FR_nni_T_R fr_nni_T_R
rename ES_nni_T_R es_nni_T_R
rename IT_nni_T_R it_nni_T_R
rename GB_nni_T_R gb_nni_T_R
rename SE_nni_T_R se_nni_T_R
rename US_nni_T_R us_nni_T_R 
rename JP_nni_T_R jp_nni_T_R
rename NO_nni_T_R no_nni_T_R
rename CA_nni_T_R ca_nni_T_R

 
 drop if year < 1950
 drop if year > 2019


/// Generate own real housprice index with CPI and compare it with the OECD real house price index

foreach var in au ca ch de es fr gb it jp no se us {

	gen `var'_houseP_real_cpi = `var'_houseP_nom / `var'_cpi * `var'_cpi[61]

	label var `var'_houseP_real_cpi "real house price index - own index with CPI"
}



set scheme s1color

** Compare own real housprice index with oecd real houseprice index
foreach var in au ca ch de es fr gb it jp no se us {
tw (connect `var'_houseP_real_cpi year if year>=1950, lcolor(red*1.2) mcolor(red*1.2) lwidth(medthick) ) ///
	(connect `var'_houseP_real year if year>=1950, lcolor(black*1.2) mcolor(black*1.2) msymb(T) lpattern(-) lwidth(medthick)  ) ///
	, title() legend() ytitle(Housepriceindex - Real) xtitle("") /// 
	ylab(, grid) xlab(,grid)
	
//	graph export "`var'_Houseprice_real_comparison.pdf", replace
}


**** generate shareprice index in real terms (i.e. deflating with CPI)

foreach var in au ca ch de es fr gb it jp no se us {

	gen `var'_shareP_real_cpi = `var'_shareP_nom / `var'_cpi * `var'_cpi[61]

	label var `var'_shareP_real_cpi "share house price index - own index with CPI"
}

**** generate MSCI shareprice index in real terms (i.e. deflating with US CPI)
gen MSCI_real_cpi = MSCI / us_cpi * us_cpi[61]


*** drop variables not needed for regression analysis
foreach var in au ca ch de es fr gb it jp no se us {
	drop `var'_cpi
}


foreach var in au ca ch de es fr gb it jp no se us {
	drop `var'_houseP_nom
}

foreach var in au ca ch de es fr gb it jp no se us {
	drop `var'_houseP_real
}

foreach var in au ca ch de es fr gb it jp no se us {
	drop `var'_shareP_nom
}


drop MSCI


// there seems to be a data issue for norway; do not use observations for years>2016
replace no_nni_T_R=. if year>2016
replace no_houseP_real_cpi=. if year>2016
replace no_shareP_real_cpi=. if year>2016
replace no_pWIR=. if year>2016 
replace no_pWIR_offici=. if year>2016 


save "house_price_final_data.dta", replace
clear all
cd "$mypath/"


